/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.1.3                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          Project4 dezignv41.dez                          */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2012-04-05 13:26                                */
/* ---------------------------------------------------------------------- */
---Drop database KBC_Quiz
  Create database KBC_Quiz

  go

  use KBC_Quiz
go

/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "User_Account"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [User_Account] (
    [ID] INTEGER identity(0,1),
    [Role_ID] INTEGER,
    [Username] VARCHAR(50),
    [Password] VARCHAR(50),
    [Name] VARCHAR(50),
    [Phone_No] INTEGER,
    [Email] VARCHAR(100),
    [Ammount] FLOAT,
    [Last_Played] DATETIME,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_User_Account] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Question"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Question] (
    [ID] INTEGER IDENTITY(1,1) NOT NULL,
    [Category_ID] INTEGER NOT NULL,
    [Content] VARCHAR(Max),
    [Status] INTEGER,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Question] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Answer"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Answer] (
   [ID] INTEGER identity(1,1) NOT NULL,
    [Question_ID] INTEGER,
    [Content] VARCHAR(500),
    [Correct] INTEGER,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Answer] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "User_Quest"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [User_Quest] (
    [User_ID]INTEGER NOT NULL,
    [Question_ID] INTEGER NOT NULL,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_User_Quest] PRIMARY KEY ([User_ID], [Question_ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Prize_Format"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Prize_Format] (
    [ID] INTEGER IDENTITY(1,1) NOT NULL,
    [Right_Ans] FLOAT,
    [Miss_Ans] FLOAT,
    [Walk_Away] FLOAT,
    [Wrong_Ans] FLOAT,
    [Last_Update] VARCHAR(40),
    CONSTRAINT [PK_Prize_Format] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Role"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Role] (
   [ID] INTEGER identity(0,1) NOT NULL,
    [Role_Name] VARCHAR(40),
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Role] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Role_Menu"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Role_Menu] (
    [Role_ID] INTEGER identity(0,1) NOT NULL,
    [Menu_ID] INTEGER NOT NULL,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Role_Menu] PRIMARY KEY ([Role_ID], [Menu_ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Menu"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Menu] (
    [ID] INTEGER identity(0,1) NOT NULL,
    [Menu_Name] VARCHAR(50),
    [Link] VARCHAR(500),
    [Parent_ID] INTEGER,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Menu] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Category_Question"                                          */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Category_Question] (
    [ID] INTEGER IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [Last_Update] DATETIME,
    CONSTRAINT [PK_Category_Question] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [User_Account] ADD CONSTRAINT [Role_User_Account] 
    FOREIGN KEY ([Role_ID]) REFERENCES [Role] ([ID])
GO

ALTER TABLE [Question] ADD CONSTRAINT [Category_Question_Question] 
    FOREIGN KEY ([Category_ID]) REFERENCES [Category_Question] ([ID])
GO

ALTER TABLE [Answer] ADD CONSTRAINT [Question_Answer] 
    FOREIGN KEY ([Question_ID]) REFERENCES [Question] ([ID])
GO

ALTER TABLE [User_Quest] ADD CONSTRAINT [User_Account_User_Quest] 
    FOREIGN KEY ([User_ID]) REFERENCES [User_Account] ([ID])
GO

ALTER TABLE [User_Quest] ADD CONSTRAINT [Question_User_Quest] 
    FOREIGN KEY ([Question_ID]) REFERENCES [Question] ([ID])
GO

ALTER TABLE [Role_Menu] ADD CONSTRAINT [Role_Role_Menu] 
    FOREIGN KEY ([Role_ID]) REFERENCES [Role] ([ID])
GO

ALTER TABLE [Role_Menu] ADD CONSTRAINT [Menu_Role_Menu] 
    FOREIGN KEY ([Menu_ID]) REFERENCES [Menu] ([ID])
GO
